import pymysql
import re
def idinput(string):
  ID=input(string)
  pattern=re.compile("^\d{1,3}$")
  while not re.match(pattern,ID):
    print("输入错误，请重新输入")
    ID=input("请输入1~3位整数：")
  return ID

# 增加学生
def appendStudentInfo():
  ID=idinput("请输入学号：")
  db=pymysql.connect(host='localhost', port=3306, user='root', passwd='liqian', db='math_score')
  cursor=db.cursor()
  sql="select * from delstudentpython where ID='%d'"%int(ID)
  cursor.execute(sql)
  while cursor.rowcount>0:
    ID = idinput("该学号已存在，请重新输入:")
    sql="select * from delstudentpython where ID='%d'"%int(ID)
    cursor.execute(sql)
  name = input("请输入学生姓名:")

  chinese = input("请输入学生语文成绩:")
  while not chinese.isdigit() or int(chinese)<0 or int(chinese)>100:
    chinese = input("输入有误，请重新输入:")

  math = input("请输入学生数学成绩:")
  while not math.isdigit() or int(math)<0 or int(math)>100:
    math = input("输入有误，请重新输入:")

  english = input("请输入学生英语成绩:")
  while not english.isdigit() or int(english)<0 or int(english)>100:
    english = input("输入有误，请重新输入:")

  total=int(chinese)+int(math)+int(english)
  sql="""INSERT INTO delstudentpython(ID,NAME,CHINESE,MATH,ENGLISH,TOTAL) VALUES(%s,%s,%s,%s,%s,%s)"""
  cursor.execute(sql,(ID,name,chinese,math,english,total))
  db.commit()
  db.close()

# 查询学生信息
def queryStudent(ID):
  db=pymysql.connect(host='localhost', port=3306, user='root', passwd='liqian', db='math_score')
  cursor=db.cursor()
  sql="""SELECT * FROM delstudentpython WHERE ID=%s"""
  cursor.execute(sql,(ID))
  if cursor.rowcount ==0:
    print("无此学生信息")
    return False
  else:
    result=cursor.fetchone()
    print("ID:%s\t姓名:%s\t语文:%s\t英语:%s\t数学:%s\t总分:%s"%result)
    return True
# 删除学生信息
def delStudent():
  ID=idinput("请输入要删除学生的学号:")
  if queryStudent(ID):
    select = input("是否删除该学生信息(Y/N):")
    if select=='Y' or select=='y':
      db=pymysql.connect(host='localhost', port=3306, user='root', passwd='liqian', db='math_score')
      cursor=db.cursor()
      sql="""DELETE FROM delstudentpython WHERE ID=%s"""
      cursor.execute(sql,(ID))
      db.commit()
      print("删除成功！")
    elif select == "N" or select =="n":
      print("取消删除！")
  else:
    print("输入错误")

# 修改学生信息
def modifystudent():
  ID=idinput("请输入要修改的学生ID：")
  if queryStudent(ID):
    name = input("请输入学生姓名：")
    chinese = input("请输入语文成绩：")
    math = input("请输入数学成绩：")
    english = input("请输入英语成绩：")
    total = int(chinese) + int(math) + int(english)
    db=pymysql.connect(host='localhost', port=3306, user='root', passwd='liqian', db='math_score')
    cursor=db.cursor()
    sql="update delstudentpython set name=%s,chinese=%s,math=%s,english=%s,total=%s where ID=%s"
    cursor.execute(sql,(name,chinese,math,english,total,ID))
    db.commit()
    db.close()
  else:
    print("输入有误")

# 列出全部学生
def allinfo():
  db=pymysql.connect(host='localhost', port=3306, user='root', passwd='liqian', db='math_score')
  cursor=db.cursor()
  sql="select * from delstudentpython"
  cursor.execute(sql)
  result=cursor.fetchall()
  for row in result:
    ID=row[0]
    name=row[1]
    chinese=row[2]
    english=row[3]
    math=row[4]
    total=row[5]
    print("ID:",ID,"姓名:",name,"语文成绩:",chinese,"数学成绩:",math,"英语成绩:",english,"总成绩:",total)

# 主程序
def studentMenu():
  print("="*30)
  print("学生管理系统")
  print("1.添加学生信息")
  print("2.删除学生信息")
  print("3.查询学生信息")
  print("4.修改学生信息")
  print("5.全部学生信息")
  print("6.退出")
  print("="*30)

if __name__ == '__main__':
  while True:
    studentMenu()
    menuindex=input("请输入选项序号：")
    while not menuindex.isdigit():
      menuindex=input("请输入选项序号：")
    if int(menuindex)==1:
      appendStudentInfo()
    elif int(menuindex)==2:
      delStudent()
    elif int(menuindex)==3:
      queryid=idinput("请输入要查询的学号：")
      queryStudent(queryid)
    elif int(menuindex)==4:
      modifystudent()
    elif int(menuindex)==5:
      allinfo()
    elif int(menuindex)==6:
      break # 退出最内层的循环
    else:
      print("输入错误，请重新输入！")
